package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import POJO.Box;
import POJO.CleaningBox;
import POJO.Parcel;
import POJO.Terminal;
import Utils.TimeHelper;

@Repository
public class BoxDao {
	@Autowired
	private GoodsDao goodsDao;
	@Autowired
	private ParcelDao parcelDao;
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	/**
	 * 写入箱格异常日志表
	 * @param parcel
	 * @param logID
	 * @param comment
	 * @param reason
	 * @return
	 * by zgy
	 */
	public int saveAbnormalBoxLog(final Parcel parcel,final int logID,final String comment,final String reason) {
		final String sql = "INSERT INTO log_box_abnormal "
				+ "(`terminalID`, `boxNum`, `log_parcel_abnormalID` ,`comment`,`reason`,`time`)"
				+ " VALUES (?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, parcel.getTerminalId());
				ps.setString(2, parcel.getBoxSeq());
				ps.setInt(3, logID);
				ps.setString(4, comment);
				ps.setString(5, reason);
				ps.setTimestamp(6, new Timestamp(new Date().getTime()));
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	
	/**
	 * 写入箱格异常日志（重载）
	 * @param terminalID
	 * @param boxNum
	 * @param logID
	 * @param comment
	 * @param reason
	 * @return
	 * by zgy
	 */
	public int saveAbnormalBoxLog(final int terminalID,final String boxNum,final int logID,final String comment,final String reason) {
		final String sql = "INSERT INTO log_box_abnormal "
				+ "(`terminalID`, `boxNum`, `log_parcel_abnormalID` ,`comment`,`reason`,`time`)"
				+ " VALUES (?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, terminalID);
				ps.setString(2, boxNum);
				ps.setInt(3, logID);
				ps.setString(4, comment);
				ps.setString(5, reason);
				ps.setTimestamp(6, new Timestamp(new Date().getTime()));
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	
	public List<Box> getAvailableBox(Terminal terminal){
		final ArrayList<Box> boxes=new ArrayList<Box>();
		String getAvailableBoxSQL="SELECT boxStatus_boxNum,boxStatus_boxTypeID FROM boxstatus"
				+ " WHERE boxStatus_status=0 AND boxStatus_isdelete=0 and "
				+ "boxStatus_terminalID=?";
		Object[] params=new Object[]{terminal.getTerminalID()};
		jdbcTemplate.query(getAvailableBoxSQL, params,new RowCallbackHandler(){

			public void processRow(ResultSet rs) throws SQLException {
				Box box=new Box();
				box.setBoxSeq(rs.getInt("boxStatus_boxNum"));
				box.setBoxTypeID(rs.getInt("boxStatus_boxTypeID"));
				boxes.add(box);
			}
		});
		if(boxes.isEmpty()){
			return null;
		}else{
			return boxes;
		}
	}
	
	/**
	 * @param terminal
	 * @param boxSeq
	 * @param toggle 0-available 1-unavailable
	 * @return true-success false-fail
	 * @throws Exception
	 */	
	public boolean boxToggleAvailable(Terminal terminal,String boxSeq,int toggle){
		String boxToggleAvailableSql="UPDATE `boxstatus` SET `boxStatus_status`=? "
				+ "WHERE boxStatus_terminalID=? and boxStatus_boxNum=?";
		Object[] params=new Object[]{toggle,terminal.getTerminalID(),boxSeq};
		int result=jdbcTemplate.update(boxToggleAvailableSql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	public boolean boxToggleAvailable(Terminal terminal,String boxSeq,int toggle,int goodsID,int parcelID){
		String boxToggleAvailableSql="UPDATE `boxstatus` SET `boxStatus_status`=?,`boxStatus_parcelID`=?,`boxStatus_goodsID`=? "
				+ "WHERE boxStatus_terminalID=? and boxStatus_boxNum=?";
		Object[] params=new Object[]{toggle,parcelID,goodsID,terminal.getTerminalID(),boxSeq};
		int result=jdbcTemplate.update(boxToggleAvailableSql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 修改物品外键
	 * @param terminalID
	 * @param boxNum
	 * @param goodsID
	 * @return
	 */
	public boolean setBoxGoods(int terminalID,int boxNum,int goodsID){
		String sql = "update boxstatus set boxStatus_goodsID=? where boxStatus_terminalID=? and boxStatus_boxNum=?";
		Object[] params=new Object[]{goodsID,terminalID,boxNum};
		int result=jdbcTemplate.update(sql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 修改箱格物品外键
	 * @param terminalID
	 * @param boxNum
	 * @param parcelID
	 * @return
	 */
	public boolean setBoxParcel(int terminalID,int boxNum,int parcelID){
		String sql = "update boxstatus set boxStatus_parcelID=? where boxStatus_terminalID=? and boxStatus_boxNum=?";
		Object[] params=new Object[]{parcelID,terminalID,boxNum};
		int result=jdbcTemplate.update(sql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 更新箱格状态
	 * @param terminalID
	 * @param boxNum
	 * @param status
	 * @return
	 */
	public boolean updateBoxStatus(int terminalID,int boxNum,int status,Timestamp timestamp){
		String sql="UPDATE `boxstatus` SET `boxStatus_status`=?,`boxStatus_changeTime`=? "
				+ "WHERE boxStatus_terminalID=? and boxStatus_boxNum=?";
		Object[] params=new Object[]{status,timestamp,terminalID,boxNum};
		int result=jdbcTemplate.update(sql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	/**
	 * 请求强制开箱
	 * @param terminalID
	 * @param boxSeq
	 * @return
	 */
	public boolean forceOpenBox(int terminalID,int boxSeq){
		String sql="UPDATE `boxstatus` SET `boxStatus_forceOpen`='1'" +
				" WHERE `boxStatus_terminalID`=? and `boxStatus_boxNum`=?;";
		Object[] params=new Object[]{terminalID,boxSeq};
		int result=jdbcTemplate.update(sql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	
	/**
	 * 请求强制开箱
	 * @param terminalID
	 * @param boxSeq
	 * @return
	 */
	public boolean forceOpenBox(int terminalID,int boxSeq,int effectTime){
		String sql="UPDATE `boxstatus` SET `boxStatus_forceOpen`='1',`boxStatus_effectTime`=?" +
				" WHERE `boxStatus_terminalID`=? and `boxStatus_boxNum`=?;";
		if(effectTime<0||effectTime>5)effectTime=5;
		Timestamp timestamp = new Timestamp(TimeHelper.getDeadTime(new Date(), effectTime));
		Object[] params=new Object[]{timestamp,terminalID,boxSeq};
		int result=jdbcTemplate.update(sql,params);
		if(result==1){
			return true;
		}else{
			return false;
		}
	}
	
	
	public ArrayList<Box> returnForceOpenBoxes(Terminal terminal){
		final ArrayList<Box> boxes=new ArrayList<Box>();
		String returnForceOpenBoxesSql="SELECT boxStatus_parcelID,boxStatus_goodsID,boxStatus_boxNum,boxStatus_effectTime FROM boxstatus " +
				"WHERE boxStatus_forceOpen=1 AND boxStatus_terminalID=?;";
		Object[] params=new Object[]{terminal.getTerminalID()};

		jdbcTemplate.query(returnForceOpenBoxesSql, params,new RowCallbackHandler(){

			public void processRow(ResultSet rs) throws SQLException {
				int parcelID = rs.getInt("boxStatus_parcelID");
				int goodsID = rs.getInt("boxStatus_goodsID");
				Box box=new Box();
				box.setBoxSeq(rs.getInt("boxStatus_boxNum"));
				box.setExceedTime(new Timestamp(rs.getTimestamp("boxStatus_effectTime").getTime()/1000));
				if(parcelID!=0){box.setItemNum(parcelDao.getParcelbyId(parcelID).getParcelNum());}
				if(goodsID!=0){box.setItemNum(goodsDao.getOrderNoByGoodsID(goodsID));}
				boxes.add(box);
				System.out.println("查询到远程开箱箱格");
			}
		});
		if(boxes.isEmpty()){
			return null;
		}else{
			return boxes;
		}

	}
	
	public ArrayList<CleaningBox> returnCleaningBox(Terminal terminal){
		final ArrayList<CleaningBox> cleaningBoxs=new ArrayList<CleaningBox>();
		String sql="SELECT boxStatus_parcelID,boxStatus_goodsID,boxStatus_boxNum FROM boxstatus " +
				"WHERE boxStatus_forceOpen=2 AND boxStatus_terminalID=?;";
		Object[] params=new Object[]{terminal.getTerminalID()};
		
		jdbcTemplate.query(sql, params,new RowCallbackHandler(){
			
			public void processRow(ResultSet rs) throws SQLException {
				int parcelID = rs.getInt("boxStatus_parcelID");
				int goodsID = rs.getInt("boxStatus_goodsID");
				CleaningBox cleaningBox=new CleaningBox();
				cleaningBox.setBoxSeq(rs.getInt("boxStatus_boxNum"));
				if(parcelID!=0){cleaningBox.setItemNum(parcelDao.getParcelbyId(parcelID).getParcelNum());}
				if(goodsID!=0){cleaningBox.setItemNum(goodsDao.getOrderNoByGoodsID(goodsID));}
				cleaningBoxs.add(cleaningBox);
//				System.out.println("查询到清空");
			}
		});
		if(cleaningBoxs.isEmpty()){
			return null;
		}else{
			return cleaningBoxs;
		}
		
	}
	
	
	
	public void updateForceOpenBoxStatus(Terminal terminal,int boxSeq){
		String updateForceOpenBoxStatusSql="UPDATE `boxstatus` SET `boxStatus_forceOpen`='0',`boxStatus_parcelID`='0',`boxStatus_goodsID`='0'" +
				" WHERE `boxStatus_terminalID`=? and `boxStatus_boxNum`=?;";
		Object[] params=new Object[]{terminal.getTerminalID(),boxSeq};
		jdbcTemplate.update(updateForceOpenBoxStatusSql,params);
	}

	/**
	 * 返回指定终端箱号的类型
	 * @param oldBoxSeq
	 * @param terminal
	 * @return
	 */
	
	public int getBoxType(int oldBoxSeq, Terminal terminal) {
		String getBoxTypeSql="select boxStatus_boxTypeID from boxstatus where boxStatus_boxNum=? and boxStatus_terminalID=?";
		Object[] params=new Object[]{oldBoxSeq,terminal.getTerminalID()};
		int boxType=-1;
		try {
			boxType=jdbcTemplate.queryForInt(getBoxTypeSql,params);
		} catch (Exception e) {
		}
		return boxType;
	}
	/**
	 * 获取箱格开箱超时时间
	 * @param boxSeq
	 * @param terminal
	 * @return
	 */
	public Timestamp getBoxDeadTime(int boxSeq, Terminal terminal) {
		String getBoxTypeSql="select boxStatus_deadTime from boxstatus where boxStatus_boxNum=? and boxStatus_terminalID=?";
		Object[] params=new Object[]{boxSeq,terminal.getTerminalID()};
		Timestamp timestamp = null;
		try {
			timestamp=jdbcTemplate.queryForObject(getBoxTypeSql,params,Timestamp.class);
		} catch (Exception e) {
		}
		return timestamp;
	}

	/**
	 * 返回大于一个指定类型的箱号
	 * @param boxType
	 * @param terminal
	 * @return 如果没有该类型空箱返回-1
	 */
	public int getBoxSeqByBoxType(int boxType,Terminal terminal) {
		String getBoxSeqByBoxTypeSql="SELECT boxStatus_boxNum FROM boxstatus WHERE boxStatus_boxTypeID>=? AND boxStatus_terminalID=? and boxStatus_status=0 limit 1";
		Object[] params=new Object[]{boxType,terminal.getTerminalID()};
		int boxSeq=-1;
		try {
			boxSeq=jdbcTemplate.queryForInt(getBoxSeqByBoxTypeSql,params);
		} catch (Exception e) {
		}
		return boxSeq;
	}

	/**
	 * 返回指定类型的箱号
	 * @param boxType
	 * @param terminal
	 * @return 如果没有该类型空箱返回-1
	 */
	public int getBoxSeqByCertainBoxType(int boxType,int terminalId) {
		String getBoxSeqByBoxTypeSql="SELECT boxStatus_boxNum FROM boxstatus " +
				" WHERE boxStatus_boxTypeID=? AND boxStatus_terminalID=? and boxStatus_status=0 limit 1";
		Object[] params=new Object[]{boxType,terminalId};
		int boxSeq=-1;
		try {
			boxSeq=jdbcTemplate.queryForInt(getBoxSeqByBoxTypeSql,params);
		} catch (Exception e) {
		}
		return boxSeq;
	}
	
	/**
	 * 判断箱子是否可用
	 * @param terminal
	 * @param boxNum
	 * @return
	 */
	public boolean isBoxAvailable(Terminal terminal,int boxNum){
		
		String isBoxAvailableSql="SELECT boxStatus_status FROM boxstatus WHERE boxStatus_boxNum=? and boxStatus_terminalID=?;";
		Object[] params=new Object[]{boxNum,terminal.getTerminalID()};
		int boxStatus=-1;
		try {
			boxStatus=jdbcTemplate.queryForObject(isBoxAvailableSql,params,Integer.class);
		} catch (Exception e) {
			//e.printStackTrace();
		}
		
		if(boxStatus==0){
			return true; 
		}else{
			return false;
		}
	}
	
	/**
	 * 查询设备箱格状态
	 * @param terminal
	 * @param boxNum
	 * @return
	 * by zgy
	 */
	public int queryBoxStatus(Terminal terminal,int boxNum){
		String isBoxAvailableSql="SELECT boxStatus_status FROM boxstatus WHERE boxStatus_boxNum=? and boxStatus_terminalID=?;";
		Object[] params=new Object[]{boxNum,terminal.getTerminalID()};
		int boxStatus=-1;
		try {
			boxStatus=jdbcTemplate.queryForObject(isBoxAvailableSql,params,Integer.class);
		} catch (Exception e) {
			//e.printStackTrace();
		}
		return boxStatus;
	}
	
	/**
	 * 查询箱格使用情况
	 * @param terminalID
	 * @param boxTypeID
	 * @param status
	 * @return
	 */
	public int getBoxTypeStatusList(int terminalID,int boxTypeID,int status){
		String sql = null;
		if(status==0){
			sql = "select count(boxStatus_id) from boxstatus where boxStatus_boxTypeID=? and boxStatus_terminalID=? and boxStatus_status=0;";
		}else{
			sql = "select count(boxStatus_id) from boxstatus where boxStatus_boxTypeID=? and boxStatus_terminalID=? and boxStatus_status<>0;";
		}
		Object[] params=new Object[]{boxTypeID,terminalID};
		return jdbcTemplate.queryForInt(sql,params);
	}
	
}
